/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.OutputDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.model.OvPercent;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class OutputDaoImpl extends BaseDaoImpl implements OutputDao {
	public List getList(long bbItemId) {
		String sql = "select c.processId,c.makerName,sum(c.percent) from com.minierp.model.Output c,com.minierp.model.WorkProcess p where c.processId=p.id and c.bbItemId="
				+ bbItemId
				+ " group by c.processId,c.makerId,c.makerName"
				+ " order by p.sequenceCode";
		return this.getHibernateTemplate().find(sql);
	}

	public int delete(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.Output where id=:id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int deleteForBbItem(final long bbItemId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.Output where bbItemId=:bbItemId";
						return new Integer(session.createQuery(hql)
								.setLong("bbItemId", bbItemId).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	private String getCountSql(Date startDate, Date endDate, long wcId,
			long depId, long proId, String makerName, long mtId,
			boolean selfOutput, boolean selfEst, Date allowStart,
			boolean checkWc, long tsId, UserBean ub) {
		String str = "";
		String tableStr = " output o left join business_bill_item i on i.id=o.bb_item_id left join business_bill b on b.id=i.bill_id left join business_bill_ext be on b.id=be.id left join staffer s on s.id=o.maker_id";
		if (depId != 0L) {
			tableStr = tableStr
					+ " left join department d on d.id=s.department_id";
			str = str + " and (d.id=" + depId
					+ " or (d.parent_id_str like \'%;" + depId + ";%\'))";
		}

		if (selfEst) {
			tableStr = tableStr
					+ " left join establishment e on b.client_id=e.id";
			str = str + " and e.manager_id=" + ub.getStaffer().getId();
		}

		if (mtId != 0L) {
			tableStr = tableStr
					+ " left join make_type t on i.make_type_id=t.id";
			str = str + " and (t.id=" + mtId + " or (t.parent_id_str like \'%;"
					+ mtId + ";%\'))";
		}

		if (startDate != null) {
			str = str + " and o.make_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and o.make_date<:endDate";
		}

		String sql;
		if (proId != 0L) {
			str = str + " and o.process_id=" + proId;
		} else if (wcId != 0L) {
			str = str + " and i.wc_id=" + wcId;
		} else if (checkWc) {
			sql = EncCommonHelper.createWcSQLStr("i.wc_id", ub, false);
			if (sql != null && !sql.equals("")) {
				str = str + " and " + sql;
			}
		}

		if (makerName != null) {
			makerName = makerName.trim();
		}

		if (makerName != null && !makerName.equals("")) {
			str = str + " and (o.maker_name like :makerName)";
		}

		if (selfOutput) {
			str = str + " and o.maker_id=" + ub.getStaffer().getId();
		}

		if (allowStart != null) {
			str = str + " and b.order_date>:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			sql = EncCommonHelper.createTallySetSQLStr("b.tally_set_id", ub,
					false);
			if (sql != null && !sql.equals("")) {
				str = str + " and " + sql;
			}
		}

		sql = "select wc_id,process_id,maker_name,make_type_id,pro_name,unit,sum(amount),department_id from ( select i.wc_id,o.process_id,o.maker_id,o.maker_name,i.make_type_id,i.pro_name,\'平方米\' as unit,o.percent*i.square_num as amount,s.department_id from "
				+ tableStr
				+ " where be.enabled=\'Y\'"
				+ " and i.price_type=\'AREA\'"
				+ str
				+ " union all "
				+ " select i.wc_id,o.process_id,o.maker_id,o.maker_name,i.make_type_id,i.pro_name,case when i.leng_type=\'M\' then \'米\' else \'公分\' end as unit,o.percent*i.square_num as amount,s.department_id from "
				+ tableStr
				+ " where be.enabled=\'Y\'"
				+ " and i.price_type=\'LENGTH\'"
				+ str
				+ " union all "
				+ " select i.wc_id,o.process_id,o.maker_id,o.maker_name,i.make_type_id,i.pro_name,i.unit,o.percent*i.amount as amount,s.department_id from "
				+ tableStr
				+ " where be.enabled=\'Y\'"
				+ " and i.price_type=\'AMOUNT\'"
				+ str
				+ ") a"
				+ " group by wc_id,process_id,maker_id,maker_name,make_type_id,pro_name,unit,department_id";
		return sql;
	}

	private void setCountBlear(SQLQuery q, Date startDate, Date endDate,
			String makerName, Date allowStart) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (makerName != null) {
			makerName = makerName.trim();
		}

		if (makerName != null && !makerName.equals("")) {
			q.setString("makerName", "%" + makerName + "%");
		}

		if (allowStart != null) {
			q.setDate("allowStart", allowStart);
		}

	}

	public int getCountTotal(Date startDate, Date endDate, long wcId,
			long depId, long proId, String makerName, long mtId,
			boolean selfOutput, boolean selfEst, Date allowStart,
			boolean checkWc, long tsId, UserBean ub) {
		String sql = this.getCountSql(startDate, endDate, wcId, depId, proId,
				makerName, mtId, selfOutput, selfEst, allowStart, checkWc,
				tsId, ub);
		sql = "select count(*) from (" + sql + ") aa";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setCountBlear(q, startDate, endDate, makerName, allowStart);
			List list = q.list();
			if (list != null && !list.isEmpty()) {
				int arg23 = Integer.parseInt(list.get(0).toString());
				return arg23;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List getCountList(Date startDate, Date endDate, long wcId,
			long depId, long proId, String makerName, long mtId,
			boolean selfOutput, boolean selfEst, Date allowStart,
			boolean checkWc, long tsId, UserBean ub, int startIndex, int num) {
		String sql = this.getCountSql(startDate, endDate, wcId, depId, proId,
				makerName, mtId, selfOutput, selfEst, allowStart, checkWc,
				tsId, ub);
		sql = sql + " order by maker_name";
		Session sess = null;

		List arg24;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setCountBlear(q, startDate, endDate, makerName, allowStart);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg24 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg24;
	}

	private String getOvBusiListSql(Date startDate, Date endDate,
			String bbCode, String cont, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.OvPercent op,com.minierp.model.Establishment e";
		String str = " where b.id=be.id and b.id=op.billId and b.clientId=e.id";
		if (depId != 0L) {
			tableStr = tableStr
					+ ",com.minierp.model.Staffer s,com.minierp.model.Department d";
			str = str
					+ " and b.managerId=s.id and s.departmentId=d.id and (d.id="
					+ depId + " or (d.parentIdStr like \'%;" + depId + ";%\'))";
		}

		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (b.billCode like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (b.content like :cont)";
		}

		if (staId != 0L) {
			str = str + " and b.managerId=" + staId;
		}

		if (selfOutput) {
			str = str + " and b.managerId=" + ub.getStaffer().getId();
		} else {
			str = str + " and b.managerId!=0";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.tallySetId",
					ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		return " from " + tableStr + str;
	}

	private void setOvBusiListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

	}

	public List getOvBusiList(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub, int startIndex, int num) {
		String str = this.getOvBusiListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select b.id,b.orderDate,b.billCode,e.estName,b.content,b.cost,op.busiOv,b.managerName,b.cost*op.busiOv as ov "
				+ str + " order by b.orderDate desc,b.id desc";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvBusiListBlear(q, startDate, endDate, bbCode, cont);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getOvBusiListCount(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub) {
		String str = this.getOvBusiListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(b.cost*op.busiOv),sum(b.cost) " + str;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvBusiListBlear(q, startDate, endDate, bbCode, cont);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	private String getOvFollListSql(Date startDate, Date endDate,
			String bbCode, String cont, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.OvPercent op,com.minierp.model.Establishment e,com.minierp.model.FollowDetail dd,com.minierp.model.Staffer s";
		String str = " where b.id=be.id and b.id=op.billId and b.clientId=e.id and b.id=dd.billId and dd.stafferId=s.id";
		if (depId != 0L) {
			tableStr = tableStr + ",com.minierp.model.Department d";
			str = str + " and s.departmentId=d.id and (d.id=" + depId
					+ " or (d.parentIdStr like \'%;" + depId + ";%\'))";
		}

		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (b.billCode like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (b.content like :cont)";
		}

		if (staId != 0L) {
			str = str + " and s.id=" + staId;
		}

		if (selfOutput) {
			str = str + " and dd.stafferId=" + ub.getStaffer().getId();
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.tallySetId",
					ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		return " from " + tableStr + str;
	}

	private void setOvFollListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

	}

	public List getOvFollList(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub, int startIndex, int num) {
		String str = this.getOvFollListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select b.id,b.orderDate,b.billCode,e.estName,b.content,b.cost,dd.percent,s.stafferName,b.cost*dd.percent*op.follOv as ov,op.follOv "
				+ str + " order by b.orderDate desc,b.id desc";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvFollListBlear(q, startDate, endDate, bbCode, cont);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getOvFollListCount(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub) {
		String str = this.getOvFollListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(b.cost*dd.percent*op.follOv),sum(b.cost) "
				+ str;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvFollListBlear(q, startDate, endDate, bbCode, cont);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	private String getOvSaleListSql(Date startDate, Date endDate,
			String bbCode, String cont, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.Establishment e";
		String str = " where b.id=be.id and b.clientId=e.id";
		if (depId != 0L) {
			tableStr = tableStr
					+ ",com.minierp.model.Staffer s,com.minierp.model.Department d";
			str = str
					+ " and b.introducerId=s.id and s.departmentId=d.id and (d.id="
					+ depId + " or (d.parentIdStr like \'%;" + depId + ";%\'))";
		}

		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.orderDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.orderDate<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (b.billCode like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (b.content like :cont)";
		}

		if (staId != 0L) {
			str = str + " and b.introducerId=" + staId;
		}

		if (selfOutput) {
			str = str + " and b.introducerId=" + ub.getStaffer().getId();
		} else {
			str = str + " and b.introducerId!=0";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.tallySetId",
					ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		return " from " + tableStr + str;
	}

	private void setOvSaleListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

	}

	public List getOvSaleList(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub, int startIndex, int num) {
		String str = this.getOvSaleListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select b.id,b.orderDate,b.billCode,e.estName,b.content,b.cost,b.introducerName "
				+ str + " order by b.orderDate desc,b.id desc";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvSaleListBlear(q, startDate, endDate, bbCode, cont);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getOvSaleListCount(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub) {
		String str = this.getOvSaleListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(b.cost) " + str;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvSaleListBlear(q, startDate, endDate, bbCode, cont);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	private String getOvReceListSql(Date startDate, Date endDate,
			String bbCode, String cont, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String imprestTableStr = " business_bill b,business_bill_ext be,ov_percent op,establishment e";
		String receTableStr = " receive_bill b,ov_percent op,receive_bill_item i,establishment e";
		String imprestStr = " where b.id=be.id and b.id=op.bill_id and b.client_id=e.id and b.imprest!=0";
		String receStr = " where b.id=i.bill_id and b.client_id=e.id and i.business_bill_id=op.bill_id";
		if (depId != 0L) {
			imprestTableStr = imprestTableStr + ",staffer s,department d";
			receTableStr = receTableStr + ",staffer s,department d";
			imprestStr = imprestStr
					+ " and b.manager_id=s.id and s.department_id=d.id and (d.id="
					+ depId + " or (d.parent_id_str like \'%;" + depId
					+ ";%\'))";
			receStr = receStr
					+ " and b.manager_id=s.id and s.department_id=d.id and (d.id="
					+ depId + " or (d.parent_id_str like \'%;" + depId
					+ ";%\'))";
		}

		imprestStr = imprestStr + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		receStr = receStr + " and b.enabled=\'Y\' and b.examined=\'Y\'";
		if (startDate != null) {
			imprestStr = imprestStr + " and b.order_date>=:startDate";
			receStr = receStr + " and b.receive_date>=:startDate";
		}

		if (endDate != null) {
			imprestStr = imprestStr + " and b.order_date<:endDate";
			receStr = receStr + " and b.receive_date<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			imprestStr = imprestStr + " and (b.bill_code like :bbCode)";
			receStr = receStr + " and (i.business_bill_code like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			imprestStr = imprestStr + " and (b.content like :cont)";
			receStr = receStr + " and (b.description like :cont)";
		}

		if (staId != 0L) {
			imprestStr = imprestStr + " and b.manager_id=" + staId;
			receStr = receStr + " and b.manager_id=" + staId;
		}

		if (selfOutput) {
			imprestStr = imprestStr + " and b.manager_id="
					+ ub.getStaffer().getId();
			receStr = receStr + " and b.manager_id=" + ub.getStaffer().getId();
		}

		String sql;
		if (tsId != 0L) {
			imprestStr = imprestStr + " and b.tally_set_id=" + tsId;
			receStr = receStr + " and b.tally_set_id=" + tsId;
		} else {
			sql = EncCommonHelper.createTallySetSQLStr("b.tally_set_id", ub,
					false);
			if (sql != null && !sql.equals("")) {
				imprestStr = imprestStr + " and " + sql;
				receStr = receStr + " and " + sql;
			}
		}

		sql = " from ( select b.id,b.order_date as bill_date,\'IMPREST\' as type,b.bill_code as bb_code,e.est_name,b.content as content,b.imprest as cost,op.rece_ov,b.manager_name,b.imprest*op.rece_ov as ov from "
				+ imprestTableStr
				+ imprestStr
				+ " union all "
				+ " select b.id,b.receive_date as bill_date,\'RECE\' as type,i.business_bill_code as bb_code,e.est_name,b.description as content,i.this_cost as cost,op.rece_ov,b.manager_name,i.this_cost*op.rece_ov as ov from "
				+ receTableStr + receStr + ") a";
		return sql;
	}

	private void setOvReceListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

	}

	public List getOvReceList(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub, int startIndex, int num) {
		String str = this.getOvReceListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select * " + str
				+ " order by bill_date desc,bb_code desc";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvReceListBlear(q, startDate, endDate, bbCode, cont);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getOvReceListCount(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub) {
		String str = this.getOvReceListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(ov),sum(cost) " + str;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvReceListBlear(q, startDate, endDate, bbCode, cont);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	private String getOvDesiListSql(Date startDate, Date endDate,
			String bbCode, String cont, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " business_bill b,business_bill_ext be,ov_percent op,establishment e,design_detail dd,staffer s";
		String str = " where b.id=be.id and b.id=op.bill_id and b.client_id=e.id and b.id=dd.bill_id and dd.staffer_id=s.id";
		if (depId != 0L) {
			tableStr = tableStr + ",department d";
			str = str + " and s.department_id=d.id and (d.id=" + depId
					+ " or (d.parent_id_str like \'%;" + depId + ";%\'))";
		}

		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (b.bill_code like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (b.content like :cont)";
		}

		if (staId != 0L) {
			str = str + " and s.id=" + staId;
		}

		if (selfOutput) {
			str = str + " and dd.staffer_id=" + ub.getStaffer().getId();
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr(
					"b.tally_set_id", ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		return " from " + tableStr + str;
	}

	private void setOvDesiListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

	}

	public List getOvDesiList(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub, int startIndex, int num) {
		String str = this.getOvDesiListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select b.id,b.order_date,b.bill_code,e.est_name,b.content,b.cost,b.design_type,dd.percent,s.staffer_name,case when b.design_type=\'SELF\' then b.cost*dd.percent*op.com_desi else b.cost*dd.percent*op.cli_desi end as ov,case when b.design_type=\'SELF\' then op.com_desi else op.cli_desi end as desiOv "
				+ str + " order by b.order_date desc,b.id desc";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvDesiListBlear(q, startDate, endDate, bbCode, cont);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getOvDesiListCount(Date startDate, Date endDate, String bbCode,
			String cont, long depId, long staId, boolean selfOutput, long tsId,
			UserBean ub) {
		String str = this.getOvDesiListSql(startDate, endDate, bbCode, cont,
				depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(case when b.design_type=\'SELF\' then b.cost*dd.percent*op.com_desi else b.cost*dd.percent*op.cli_desi end),sum(b.cost) "
				+ str;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvDesiListBlear(q, startDate, endDate, bbCode, cont);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	private String getOvMakeListSql(Date startDate, Date endDate,
			String bbCode, String cont, String mr, long depId, long wcId,
			long staId, boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " com.minierp.model.BusinessBill b,com.minierp.model.BusinessBillExt be,com.minierp.model.OvPercent op,com.minierp.model.BusinessBillItem i,com.minierp.model.Establishment e,com.minierp.model.Output dd,com.minierp.model.WorkProcess p";
		String str = " where b.id=be.id and b.id=op.billId and b.id=i.billId and i.id=dd.bbItemId and dd.processId=p.id and b.clientId=e.id";
		if (depId != 0L) {
			tableStr = tableStr
					+ ",com.minierp.model.Staffer s,com.minierp.model.Department d";
			str = str
					+ " and dd.makerId=s.id and s.departmentId=d.id and (d.id="
					+ depId + " or (d.parentIdStr like \'%;" + depId + ";%\'))";
		}

		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and dd.makeDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and dd.makeDate<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (b.billCode like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (b.content like :cont)";
		}

		if (mr != null && !mr.equals("")) {
			str = str + " and (i.makeRequire like :mr)";
		}

		if (staId != 0L) {
			str = str + " and dd.makerId=" + staId;
		}

		if (selfOutput) {
			str = str + " and dd.makerId=" + ub.getStaffer().getId();
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.tallySetId",
					ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (wcId != 0L) {
			str = str + " and i.wcId=" + wcId;
		}

		return " from " + tableStr + str;
	}

	private void setOvMakeListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont, String mr) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

		if (mr != null && !mr.equals("")) {
			q.setString("mr", "%" + mr + "%");
		}

	}

	public List getOvMakeList(Date startDate, Date endDate, String bbCode,
			String cont, String mr, long depId, long wcId, long staId,
			boolean selfOutput, long tsId, UserBean ub, int startIndex, int num) {
		String str = this.getOvMakeListSql(startDate, endDate, bbCode, cont,
				mr, depId, wcId, staId, selfOutput, tsId, ub);
		String sql = "select dd.id,dd.makeDate,b.billCode,e.estName,i.makeTypeId,i.finishedContent,i.proName,i.specification,i.unit,i.priceType,i.squareNum,i.amount,i.cost,i.wcId,p.processName,dd.percent,dd.makerName,i.cost*p.percent*dd.percent*op.makeOv as ov,i.lengType,op.makeOv,p.percent as proPerc,i.makeRequire "
				+ str + " order by dd.makeDate desc,b.billCode desc";
		Session sess = null;

		List arg22;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvMakeListBlear(q, startDate, endDate, bbCode, cont, mr);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg22 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg22;
	}

	public List getOvMakeListCount(Date startDate, Date endDate, String bbCode,
			String cont, String mr, long depId, long wcId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String str = this.getOvMakeListSql(startDate, endDate, bbCode, cont,
				mr, depId, wcId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(i.cost*p.percent*dd.percent*op.makeOv),sum(i.cost) "
				+ str;
		Session sess = null;

		List arg20;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setOvMakeListBlear(q, startDate, endDate, bbCode, cont, mr);
			arg20 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg20;
	}

	private String getBusinessOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " business_bill b,business_bill_ext be,ov_percent op where b.id=be.id and b.id=op.bill_id and be.enabled=\'Y\' and be.examined=\'Y\' ";
		String busiStr = "";
		String saleStr = "";
		String str = "";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		String sql;
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			sql = EncCommonHelper.createTallySetSQLStr("b.tally_set_id", ub,
					false);
			if (sql != null && !sql.equals("")) {
				str = str + " and " + sql;
			}
		}

		busiStr = busiStr + str;
		saleStr = saleStr + str;
		if (selfOutput) {
			busiStr = busiStr + " and b.manager_id=" + ub.getStaffer().getId();
			saleStr = saleStr + " and b.introducer_id="
					+ ub.getStaffer().getId();
		}

		sql = "select staffer_id,sum(busi_cost) as busi_cost,sum(sale_cost) as sale_cost,sum(rece_cost) as rece_cost from ( select b.manager_id as staffer_id,b.cost*op.busi_ov as busi_cost,0 as sale_cost,b.imprest*op.rece_ov as rece_cost from "
				+ tableStr
				+ busiStr
				+ " union all "
				+ " select b.introducer_id as staffer_id,0 as busi_cost,b.cost as sale_cost,0 as rece_cost from "
				+ tableStr + saleStr + ") a group by staffer_id";
		return sql;
	}

	private String getReceiveOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " receive_bill b,receive_bill_item i,ov_percent op";
		String str = " where b.id=i.bill_id and b.enabled=\'Y\' and b.examined=\'Y\' and i.business_bill_id=op.bill_id";
		if (startDate != null) {
			str = str + " and b.receive_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.receive_date<:endDate";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr(
					"b.tally_set_id", ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (selfOutput) {
			str = str + " and b.manager_id=" + ub.getStaffer().getId();
		}

		return " select b.manager_id,sum(i.this_cost*op.rece_ov) as rece_cost from "
				+ tableStr + str + " group by b.manager_id";
	}

	private String getMakeOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " business_bill b,business_bill_ext be,ov_percent op,business_bill_item i,output p,work_process wp";
		String str = " where b.id=be.id and b.id=op.bill_id and b.id=i.bill_id and i.id=p.bb_item_id and p.process_id=wp.id";
		str = str + " and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and p.make_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and p.make_date<:endDate";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr(
					"b.tally_set_id", ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (selfOutput) {
			str = str + " and p.maker_id=" + ub.getStaffer().getId();
		}

		return " select p.maker_id,sum(i.cost*p.percent*wp.percent*op.make_ov) as make_cost from "
				+ tableStr + str + " group by p.maker_id";
	}

	private String getDesignOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " business_bill b,business_bill_ext be,ov_percent op,design_detail dd";
		String str = " where b.id=be.id and b.id=op.bill_id and b.id=dd.bill_id and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		String sql;
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			sql = EncCommonHelper.createTallySetSQLStr("b.tally_set_id", ub,
					false);
			if (sql != null && !sql.equals("")) {
				str = str + " and " + sql;
			}
		}

		if (selfOutput) {
			str = str + " and dd.staffer_id=" + ub.getStaffer().getId();
		}

		sql = "select dd.staffer_id,case when b.design_type=\'SELF\' then b.cost*dd.percent*op.com_desi else b.cost*dd.percent*op.cli_desi end as d_cost from "
				+ tableStr + str;
		return " select a.staffer_id,sum(a.d_cost) as desi_cost from (" + sql
				+ ") a group by a.staffer_id";
	}

	private String getFollowOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " business_bill b,business_bill_ext be,ov_percent op,follow_detail dd";
		String str = " where b.id=be.id and b.id=op.bill_id and b.id=dd.bill_id and be.enabled=\'Y\' and be.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.order_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.order_date<:endDate";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr(
					"b.tally_set_id", ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (selfOutput) {
			str = str + " and dd.staffer_id=" + ub.getStaffer().getId();
		}

		return " select dd.staffer_id,sum(b.cost*dd.percent*op.foll_ov) as foll_cost from "
				+ tableStr + str + " group by dd.staffer_id";
	}

	private String getSetuOvCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " setu_bill b,business_bill bb,ov_percent op,setu_detail dd";
		String str = " where b.bb_id=bb.id and bb.id=op.bill_id and b.id=dd.bill_id and b.enab=\'Y\' and b.exam=\'Y\' && b.comp=\'Y\'";
		if (startDate != null) {
			str = str + " and b.real_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.real_date<:endDate";
		}

		if (tsId != 0L) {
			str = str + " and b.ts_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.ts_id", ub,
					false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (selfOutput) {
			str = str + " and dd.staffer_id=" + ub.getStaffer().getId();
		}

		return " select dd.staffer_id,sum(b.cost*dd.percent*op.setu_ov) as setu_cost from "
				+ tableStr + str + " group by dd.staffer_id";
	}

	private void setOvCountBlear(SQLQuery q, Date startDate, Date endDate) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

	}

	public List getBusinessOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getBusinessOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getReceiveOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getReceiveOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getMakeOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getMakeOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getDesignOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getDesignOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getFollowOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getFollowOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getSetuOvCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getSetuOvCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	private String getOvBillCountSql(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " ov_bill b";
		String str = " where b.enabled=\'Y\' and b.examined=\'Y\'";
		if (startDate != null) {
			str = str + " and b.out_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.out_date<:endDate";
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr(
					"b.tally_set_id", ub, false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		if (selfOutput) {
			str = str + " and b.staffer_id=" + ub.getStaffer().getId();
		}

		return " select b.staffer_id,sum(b.cost) as ov_cost from " + tableStr
				+ str + " group by b.staffer_id";
	}

	public List getOvBillCount(Date startDate, Date endDate,
			boolean selfOutput, long tsId, UserBean ub) {
		String sql = this.getOvBillCountSql(startDate, endDate, selfOutput,
				tsId, ub);
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvCountBlear(q, startDate, endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public OvPercent getOvPercent(long billId) {
		String sql = "from com.minierp.model.OvPercent as obj where obj.billId="
				+ billId;
		List list = this.getHibernateTemplate().find(sql);
		return list != null && list.size() > 0 ? (OvPercent) list.get(0) : null;
	}

	private String getOvSetuListSql(Date startDate, Date endDate,
			String bbCode, String cont, String billCode, long depId,
			long staId, boolean selfOutput, long tsId, UserBean ub) {
		String tableStr = " setu_bill b,business_bill bb,ov_percent op,establishment e,setu_detail dd,staffer s";
		String str = " where b.bb_id=bb.id and bb.id=op.bill_id and bb.client_id=e.id and b.id=dd.bill_id and dd.staffer_id=s.id";
		if (depId != 0L) {
			tableStr = tableStr + ",department d";
			str = str + " and s.department_id=d.id and (d.id=" + depId
					+ " or (d.parent_id_str like \'%;" + depId + ";%\'))";
		}

		str = str + " and b.enab=\'Y\' and b.exam=\'Y\' and b.comp=\'Y\'";
		if (startDate != null) {
			str = str + " and b.real_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.real_date<:endDate";
		}

		if (bbCode != null && !bbCode.equals("")) {
			str = str + " and (bb.bill_code like :bbCode)";
		}

		if (cont != null && !cont.equals("")) {
			str = str + " and (bb.content like :cont)";
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.bill_code like :billCode)";
		}

		if (staId != 0L) {
			str = str + " and s.id=" + staId;
		}

		if (selfOutput) {
			str = str + " and dd.staffer_id=" + ub.getStaffer().getId();
		}

		if (tsId != 0L) {
			str = str + " and b.ts_id=" + tsId;
		} else {
			String tsSql = EncCommonHelper.createTallySetSQLStr("b.ts_id", ub,
					false);
			if (tsSql != null && !tsSql.equals("")) {
				str = str + " and " + tsSql;
			}
		}

		return " from " + tableStr + str;
	}

	private void setOvSetuListBlear(Query q, Date startDate, Date endDate,
			String bbCode, String cont, String billCode) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (bbCode != null && !bbCode.equals("")) {
			q.setString("bbCode", "%" + bbCode + "%");
		}

		if (cont != null && !cont.equals("")) {
			q.setString("cont", "%" + cont + "%");
		}

		if (billCode != null && !billCode.equals("")) {
			q.setString("billCode", "%" + billCode + "%");
		}

	}

	public List getOvSetuList(Date startDate, Date endDate, String bbCode,
			String cont, String billCode, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub, int startIndex, int num) {
		String str = this.getOvSetuListSql(startDate, endDate, bbCode, cont,
				billCode, depId, staId, selfOutput, tsId, ub);
		String sql = "select b.id,b.real_date,b.bill_code,bb.bill_code as bb_code,e.est_name,bb.content,b.cost,dd.percent,s.staffer_name,b.cost*dd.percent*op.setu_ov as ov,op.setu_ov "
				+ str + " order by b.real_date desc,b.id desc";
		Session sess = null;

		List arg20;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvSetuListBlear(q, startDate, endDate, bbCode, cont,
					billCode);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg20 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg20;
	}

	public List getOvSetuListCount(Date startDate, Date endDate, String bbCode,
			String cont, String billCode, long depId, long staId,
			boolean selfOutput, long tsId, UserBean ub) {
		String str = this.getOvSetuListSql(startDate, endDate, bbCode, cont,
				billCode, depId, staId, selfOutput, tsId, ub);
		String sql = "select count(*),sum(b.cost*dd.percent*op.setu_ov),sum(b.cost) "
				+ str;
		Session sess = null;

		List arg18;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setOvSetuListBlear(q, startDate, endDate, bbCode, cont,
					billCode);
			arg18 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg18;
	}
}